package dataBase;

import java.io.UnsupportedEncodingException;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Random;
import java.util.Set;
import java.util.TimeZone;

import javax.script.SimpleScriptContext;

import sun.util.resources.CalendarData;

import org.apache.tools.ant.taskdefs.Sleep;

import model.HistoryLog;
import model.MailingManager;

import com.google.appengine.api.datastore.Blob;
import com.google.appengine.api.datastore.Email;
import com.google.appengine.api.datastore.Key;
import com.google.appengine.api.datastore.KeyFactory;
import com.google.appengine.api.datastore.PhoneNumber;
import com.google.appengine.api.datastore.Text;

public class ConnectToDataBase {
	private static ConnectToDataBase inst= new ConnectToDataBase();
	private SimpleDateFormat formatter;
	private Manager manager=new Manager();
	private Random random = new Random();

	private ConnectToDataBase(){
		formatter = new SimpleDateFormat("dd/MM/yy HH:mm");
		formatter.setTimeZone(TimeZone.getTimeZone("Israel"));
	}
	public static ConnectToDataBase getInst(){
		return inst;
	}
	/**
	 * this method adding new business to the DB
	 * @param bizName business name
	 * @param phoneNumber phone number of the business
	 * @param email email address of the business(or a contact man in the business)
	 * @param userName user name of the business account
	 * @param password password of the business account
	 * @param statusList list of wanted status of an order in the business
	 * @return 0 - success, 1 - username exists, 2 - bizname exists.
	 */
	public int newBisAccount(String bizName,String bizType, String bizNum, byte[] logoPic,PhoneNumber phoneNumber,Email email,String website, String userName,
			String password,ArrayList<String> statusList,ArrayList<Double> EtaList, ArrayList<String> messages,
			String background, String secBackground, String borderColor,String skypeLogin){
		if(getBiz(userName)!=null)
			return 1;

		if(getBizByName(bizName)!=null)
		{
			System.out.println("name exists");
			return 2;
		}

		System.out.println("1111111111");
		Business business = new Business();
		business.setBizName(bizName);
		business.setBizType(bizType);
		business.setBizNum(bizNum);

		if (logoPic!=null)
			business.setLogoPic(new Blob(logoPic));

		business.setPhoneNumber(phoneNumber);
		business.setEmail(email);
		business.setWebsite(website);
		business.setUserName(userName);
		business.setPassword(password);
		business.setNextOrderID(1);
		business.setMessages(messages);
		business.setStatusList(createStatusList(statusList,EtaList,business));
		business.setBackground(background);
		business.setSecBackground(secBackground);
		business.setBorderColor(borderColor);
		Date d = new Date();
		business.setCreateDate(d);
		business.setModifiedDate(d);
		business.setSkypeLogin(skypeLogin);
		System.out.println("22222222");

		addToHistoryLog(business, "type : \""+HistoryLog.getInst().getCREATE_BUSINESS_ACCOUNT_TYPE()+"\"", d);
		
		manager.Save(business);

		AllBiz.getInst().addNewBiz(userName, bizName,business.getKey());

		System.out.println("3333333333");
		return 0;

	}

	/**
	 * 
	 * @param bizName
	 * @param bizNum
	 * @param logoPic
	 * @param phoneNumber
	 * @param email
	 * @param userName
	 * @param password
	 * @param statusList
	 * @param EtaList
	 * @param messages
	 * @param skype 
	 * @return 0 - success, 1 - username not exists, 2 - bizname exists.
	 */
	public int updateBisAccount(String bizName, String bizType, String bizNum, byte[] logoPic,PhoneNumber phoneNumber,Email email,String website,String userName,
			String password,ArrayList<String> statusList,ArrayList<Double> EtaList, ArrayList<String> messages,
			String background, String secBackground, String borderColor, String skype,Long[] orderUrgencyTime){

		Business business = getBiz(userName);
		

		if(business==null)
		{
			System.out.println("no business. method updateBisAccount");
			return 1;
		}

		Business businessByName = getBizByName(bizName);
		System.out.println("!@#!@#!@#!@#!@# "+ bizName);
		if(businessByName!=null && (!businessByName.getUserName().equals(userName)) )
			return 2;

		String prevBizName = business.getBizName();

		business.setBizName(bizName);
		business.setBizType(bizType);
		business.setBizNum(bizNum);
		System.out.println("logo pic "+logoPic);
		if (logoPic!=null)
			business.setLogoPic(new Blob(logoPic));
		business.setPhoneNumber(phoneNumber);
		business.setEmail(email);
		business.setWebsite(website);
		business.setPassword(password);
		business.setMessages(messages);
		business.setOrderUrgencyTime(orderUrgencyTime);

		List<Key> statuses = business.getStatusList();

		if (!compareLists(statuses,statusList))
			business.setStatusList(createStatusList(statusList,EtaList,business));

		business.setBackground(background);
		business.setSecBackground(secBackground);
		business.setBorderColor(borderColor);
		Date d = new Date();
		business.setModifiedDate(d);
		if(skype!=null)
			business.setSkypeLogin(skype);
		else
			business.setSkypeLogin("");
		
		addToHistoryLog(business, "type : \""+HistoryLog.getInst().getUPDATE_BUSINESS_ACCOUNT_TYPE()+"\"", d);
		manager.Save(business);
		
		AllBiz.getInst().updateExistBiz(userName, bizName, prevBizName);
		return 0;
	}

	private boolean compareLists(List<Key> statusesKeys, ArrayList<String> statusesString)
	{
		List<String> currentStatuses = getOrderStatusName(statusesKeys);
		int size = currentStatuses.size();

		if (size!=statusesString.size())
			return false;

		String status1;
		String status2;

		for (int i=0; i<size; i++)
		{
			status1 = statusesString.get(i);
			status2 = currentStatuses.get(i);

			if (!status1.equals(status2))
				return false;		
		}

		return true;
	}

	private void deleteStatuses(List<Key> keys)
	{
		List<Status> statuses = keyToStatus(keys);
		for (Status status: statuses)
		{			
			manager.Delete(status);
		}		
	}



	private ArrayList<Key> createStatusList(ArrayList<String> statusList, ArrayList<Double> etaList, Business business) {
		ArrayList<Key> statList=new ArrayList<Key>();
		for (int i=0;i<statusList.size();i++){
			if (etaList==null)
				statList.add(newStat(statusList.get(i),i, -1.0,business).getKey());
			else
				statList.add(newStat(statusList.get(i),i,etaList.get(i),business).getKey());
		}
		return statList;

	}
	private Status newStat(String statusName, int location, Double etaTime, Business business) {
		Status newStat=new Status();
		newStat.setStatusName(statusName);
		newStat.setLocation(location);
		newStat.setTimeLeft(etaTime);
		newStat.setbusinessUserName(business.getUserName());
		manager.Save(newStat);
		return newStat;
	}

	public Business getBiz(String userName){
		@SuppressWarnings("unchecked")
		List<Business> results = (List<Business>) manager.executeJDOSQL("SELECT FROM dataBase.Business " +
				"WHERE userName == \""+userName +"\"");
		if (results.isEmpty())
			return null;
		return results.get(0);
	}

	public Business getBizByName(String bizName){
		@SuppressWarnings("unchecked")
		List<Business> results = (List<Business>) manager.executeJDOSQL("SELECT FROM dataBase.Business " +
				"WHERE bizName == \""+bizName +"\"");
		System.out.println("$$$$$$");
		if (results==null || results.isEmpty())
		{
			System.out.println("null");
			return null;
		}
		return results.get(0);
	}



	/**
	 * this method adding new Client to the DB
	 * @param userName user name of the business account
	 * @param password password of the business account
	 * @param phoneNumber phone number of the business
	 * @param email email address of the business(or a contact man in the business)
	 * @return 0 - success, 1 - client exists
	 */
	public int newClientAccount(String userName,String password,PhoneNumber phoneNumber,Email email){
		if(getClient(userName)!=null)
			return 1;
		Client client = new Client();
		client.setUserName(userName);
		client.setPassword(password);
		client.setPhoneNumber(phoneNumber);
		client.setEmail(email);

		//client.setAllOrdersList(getOrdersKeysByPhone(phoneNumber));
		List<Order> ordersByPhone = getOrdersByPhone(phoneNumber);
		for (Order order: ordersByPhone)
			addOrderToClient(client, order, null);


		Date d = new Date();
		client.setCreateDate(d);
		client.setModifiedDate(d);
		manager.Save(client);
		for (Order order: ordersByPhone){
			order.addClient(client.getKey());
			manager.Save(order);
		}

		return 0;

	}

	/**
	 * 
	 * @param userName
	 * @param password
	 * @param phoneNumber
	 * @param email
	 * @return 0 - success, 1 - client not exists
	 */
	public int updateClientAccount(String userName,String password,PhoneNumber phoneNumber,Email email){

		Client client = getClient(userName);

		if (client==null)
		{
			System.out.println("no client. method updateClientAccount");
			return 1;
		}

		client.setPassword(password);


		//client.setAllOrdersList(getOrdersKeysByPhone(phoneNumber));
		if (!client.getPhoneNumber().getNumber().equals(phoneNumber.getNumber()))
		{
			client.clearAllOrdersList();
			List<Order> ordersByPhone = getOrdersByPhone(phoneNumber);
			for (Order order: ordersByPhone){
				addOrderToClient(client, order, null);
				order.addClient(client.getKey());
			}
		}

		client.setPhoneNumber(phoneNumber);
		client.setEmail(email);
		client.setModifiedDate(new Date());
		manager.Save(client);

		return 0;

	}

	private List<Key> getOrdersKeysByPhone(PhoneNumber phoneNumber) {

		List<Order> results = getOrdersByPhone(phoneNumber);
		List<Key> keys = new ArrayList<Key>();

		for (Order o : results)
			keys.add(o.getKey());

		return keys;

	}

	public List<Order> getOrdersByPhone(PhoneNumber phoneNumber)
	{
		@SuppressWarnings("unchecked")
		List<Order> results = (List<Order>)manager.executeJDOSQL("SELECT FROM dataBase.Order " +
				"WHERE phoneNumber == \""+phoneNumber.getNumber() +"\"");

		return results;
	}

	public Client getClient(String userName) {
		@SuppressWarnings("unchecked")
		List<Client> results = (List<Client>)manager.executeJDOSQL("SELECT FROM dataBase.Client " +
				"WHERE userName == \""+userName +"\"");
		if (results.isEmpty())
			return null;
		System.out.println("not empty");
		return results.get(0);
	}
	public int BizlogIn(String userName,String password){
		Business b= getBiz(userName);
		if (b==null)
			return 1;
		if(!b.getPassword().equals(password))
			return 2;
		return 0;
	}
	public int ClientlogIn(String userName,String password){
		if(password.length()==0)
			return 2;
		Client c= getClient(userName);
		if (c==null)
			return 1;
		if(!c.getPassword().equals(password))
			return 2;
		return 0;
	}

	public boolean ClientExist(String userName) {
		Client c= getClient(userName);
		if (c==null)
			return false;
		return true;
	}



	/**
	 * 
	 * @param orderNum
	 * @param currentStatus
	 * @param commentsBiz
	 * @param address
	 * @param userNameBiz
	 * @param phone
	 * @return 0 - success, 1- order ID exists
	 */
	public int newOrder(int orderNum,int currentStatus,Text commentsBiz,String address,String userNameBiz , PhoneNumber phone,Email email,boolean sendMailBoolean, String details, Integer orderUrgency){

		if (!isOrderIdValid(orderNum,userNameBiz))
			return 1;

		Business business = getBiz(userNameBiz);

		Order order = new Order();
		order.setOrderNum(orderNum);
		order.setDestAdd(address);
		order.setComments(commentsBiz);
		order.setPhoneNumber(phone);
		order.setIsActive(true);
		order.setBusinessUserName(userNameBiz);
		order.setStatusNotification(false);
		order.setMessageNotification(false);
		order.setEmail(email.getEmail());
		order.setDetails(details);
		order.setSendMail(sendMailBoolean);
		order.setStatusList(business.getStatusList());
		order.setStatusNamesList(getOrderStatusName(business.getStatusList()));
		Date d = new Date();
		order.setStartTime(d);
		order.setMessageToRead(false);
		String dateString = formatter.format(d);
		order.setStartTimeFormat(dateString);
		System.out.println(dateString);
		order.setUrgency(orderUrgency);
		
		//order.setUserName("aaaaaa");
		Level level = new Level();
		level.setBizUserName(userNameBiz);
		level.setcurrentStatusKey(getStatus(userNameBiz,currentStatus));
		level.setCurrentStatusString(order.getStatusNamesList().get(currentStatus));
		level.setStartTime(d);
		manager.Save(level);
		
		order.addToLevelsList(level);

		manager.Save(order);

		System.out.println(order.getKey());

		int nextOrderID = Math.max(business.getNextOrderID()-1, orderNum)+1;
		business.setNextOrderID(nextOrderID);		
		business.addToAllOrdersList(order);

		addToHistoryLog(business, "type : \""+HistoryLog.getInst().getCREATE_ORDER_TYPE()+"\", \"orderNum\" : "+orderNum, d);
		
		manager.Save(business);

		List<Client> clients = getClientsByPhone(phone);
		//System.out.println();
		for (Client c: clients)
		{
			/*System.out.println("client: " + c.getUserName());
			c.addToAllOrdersList(order);
			manager.Save(c);*/
			System.out.println("client "+ c.getUserName());
			addOrderToClient(c, order, business.getBizType());
			order.addClient(c.getKey());
		}		
		manager.Save(order);
		
		return 0;
	}

	public List<String> getOrderStatusName(List<Key> statusKeys)
	{
		List<Status> statuses = keyToStatus(statusKeys);
		ArrayList<String> statusesString = new ArrayList<String>();

		for (Status s: statuses)
		{
			statusesString.add(s.getStatusName());
		}

		return statusesString;
	}

	public int getNextFreeOrderId(String userNameBiz)
	{
		Business biz = getBiz(userNameBiz);
		return biz.getNextOrderID();		
	}

	private boolean isOrderIdValid(int orderNum, String userNameBiz) {
		List<Order> orders = getOrdersofBiz(userNameBiz);
		if(orders==null)
			return false;
		for (Order o: orders)
		{
			if (o.getOrderNum()==orderNum)
				return false;
		}

		return true;

	}
	private Status getStatus(String userNameBiz, int currentStatus) {
		Business biz = getBiz(userNameBiz);
		if(biz==null)
			System.out.println("Business not found!!");
		else{
			System.out.println("$$ "+biz.getStatusList().size() + " " +keyToStatus(biz.getStatusList()).size());
			List<Status> statusList= keyToStatus(biz.getStatusList());
			for(Status s : statusList){
				if(s.getLocation() == currentStatus)
					return s;
			}
		}
		return null;
	}
	public int changeLevelInOrder(String userNameBiz,int orderNum,int newLevelNum){
		String oldStatus;
		String newStatus;

		Business biz = getBiz(userNameBiz);
		Order order = getOrder(orderNum,biz);

		System.out.println("clients order in changeLevelInOrder1: "+order.getClientList());
		if (order==null)
			return 1;

		List<Key> statusKey = order.getStatusList();
		oldStatus = getCurrentStatusOfOrder(order);
		List<Level> list = keyToLevel(order.getLevelsList());
		Level l = list.get(list.size()-1);
		Date d = new Date();
		l.setEndTime(d);
		manager.Save(l);
		Level level = new Level();
		level.setBizUserName(userNameBiz);
		level.setcurrentStatusKey(statusKey.get(newLevelNum));
		level.setCurrentStatusString(order.getStatusNamesList().get(newLevelNum));
		level.setStartTime(d);
		manager.Save(level);
		order.addToLevelsList(level);
		manager.Save(order);

		
		//newStatus = getCurrentStatusOfOrder(order);
		newStatus = order.getStatusNamesList().get(newLevelNum);
		
		addToHistoryLog(biz, "type : \""+HistoryLog.getInst().getCHANGE_ORDER_STATUS_TYPE()+"\", \"orderNum\" : "+orderNum + ", from : \""+oldStatus + "\" , to : \""+newStatus +"\"", d);
		manager.Save(biz);
		
		//System.out.println("!@#!@#!@#!@#   "+newStatus+" "+status.getStatusName());
		List<Client> clients = getClientsByPhone(order.getPhoneNumber());

		String orderEmail = order.getEmail();
		boolean sendMail = order.getSendMail();
		boolean isEmailAlreadyExist = false;
		try {
			if ((clients!=null)&&(order.getStatusNotification().booleanValue())){
				for (Client c: clients){
					if (c.getEmail().getEmail().equals(orderEmail))
						isEmailAlreadyExist=true;


					MailingManager.getInst().changeStatusMail(c.getUserName(), userNameBiz, orderNum, oldStatus, newStatus, order.getPhoneNumber().getNumber(), c.getEmail().getEmail(),false);

				}
			}

			//send also to the email that the biz added
			if (sendMail && (!orderEmail.equals("")) && (!isEmailAlreadyExist)){
				MailingManager.getInst().changeStatusMail("", userNameBiz, orderNum, oldStatus, newStatus, order.getPhoneNumber().getNumber(), orderEmail,true);
			}
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println("clients order in changeLevelInOrder2: "+order.getClientList());
		return 0;
	}

	public Order getOrder(int orderNum,String userNameBiz) {

		List<Order> allOrder = getOrdersofBiz(userNameBiz);

		if (allOrder==null)
			return null;

		for(Order o : allOrder){
			if(o.getOrderNum()==orderNum)
				return o;
		}
		return null;
	}
	public Order getOrder(int orderNum,Business Biz) {
		List<Order> allOrder = getOrdersofBiz(Biz);
		
		if (allOrder==null)
			return null;

		for(Order o : allOrder){
			if(o.getOrderNum()==orderNum)
				return o;
		}
		return null;
	}
	/**
	 * 
	 * @param orderNum
	 * @param userNameBiz
	 * @param isActive
	 * @return 0 - success, 1 - order not exist
	 */
	public int updateOrderActive(int orderNum,String userNameBiz,boolean isActive)
	{
		Business biz = getBiz(userNameBiz);
		Order o = getOrder(orderNum,biz);

		if (o==null)
			return 1;

		Date d = new Date();
		if(!isActive){
			addToHistoryLog(biz, "type : \""+HistoryLog.getInst().getCLOSE_ORDER_ACCOUNT_TYPE()+"\", \"orderNum\" : "+orderNum, d);
			manager.Save(biz);
		}
		
		o.setIsActive(isActive);
		
		if(!isActive){
			
			o.setEndTime(d);
			String dateString = formatter.format(d);
			o.setEndTimeFormat(dateString);
			
		}
		manager.Save(o);
		
		return 0;
	}

	public  List<Order> getOrdersofBiz(String userName) {

		Business b = getBiz(userName);
		System.out.println("user name: "+userName +" b: "+b);

		return getOrdersofBiz(b);
		
	}
	public  List<Order> getOrdersofBiz(Business biz) {

		if (biz==null)
		{
			System.out.println("b is null");
			return null;
		}

		List<Key> keys = biz.getAllOrdersList();

		return keyToOrders(keys);
	}
	public List<Order> getNotAvailableOrdersOfBiz(Business biz) {
		if (biz==null)
		{
			System.out.println("biz is null");
			return null;
		}

		List<Key> keys = biz.getAllOrdersList();

		List<Order> orders = keyToOrders(keys);
		List<Order> notAvailableOrders = new ArrayList<Order>();

		for (Order o: orders)
		{
			if (!o.getIsActive())
				notAvailableOrders.add(o);
		}

		return notAvailableOrders;
	}
	public  List<Order> getAvailableOrdersOfBiz(String userName) {

		Business b = getBiz(userName);

		System.out.println("user name: "+userName +" b: "+b);

		if (b==null)
		{
			System.out.println("b is null");
			return null;
		}

		List<Key> keys = b.getAllOrdersList();

		List<Order> orders = keyToOrders(keys);
		List<Order> availableOrders = new ArrayList<Order>();

		for (Order o: orders)
		{
			if (o.getIsActive())
				availableOrders.add(o);
		}

		return availableOrders;
	}


	public List<Order> keyToOrders(List<Key> keys){
		List<Order> newOrders = new ArrayList<Order>();
		Order tmp=new Order();

		for(Key key: keys){
			newOrders.add((Order)manager.getObjectById(tmp, key));
		}
		/*System.out.println("begin");
		@SuppressWarnings("unchecked")
		List<Order> orders = (List<Order>) manager.executeJDOSQL("SELECT FROM dataBase.Order");
		List<Order> newOrders = new ArrayList<Order>();

		int numOfOrders = keys.size();
		Order[] newOrdersBuffer = new Order[numOfOrders];

		int indexOfKey;

		for(Order currentOrder:orders)
		{
			indexOfKey = keys.indexOf(currentOrder.getKey());

			if (indexOfKey!=-1)
			{
				newOrdersBuffer[indexOfKey] = currentOrder;			
			}
		}

		for (int i=0; i<numOfOrders; i++)
		{
			if (newOrdersBuffer[i]!=null)
				newOrders.add(newOrdersBuffer[i]);
		}

		System.out.println("end fun");	*/	
		System.out.println("MY KEYSTOORDER");
		return newOrders;

	}

	public List<Ad> keyToAds(List<Key> keys)
	{
		List<Ad> newAds = new ArrayList<Ad>();
		Ad tmp=new Ad();

		for(Key key: keys){
			newAds.add((Ad)manager.getObjectById(tmp, key));
		}
		return newAds;
	}

	public Business keyToBusiness(long id)
	{

		Business tmp=new Business();	
		return (Business)manager.getObjectById(tmp, id);
	}
	public Ad keyToAd(long id)
	{

		Ad tmp=new Ad();	
		return (Ad)manager.getObjectById(tmp, id);
	}
	public List<Status> keyToStatus(List<Key> keys)
	{
		List<Status> newStatuses = new ArrayList<Status>();
		Status tmp=new Status();

		for(Key key: keys){
			newStatuses.add((Status)manager.getObjectById(tmp, key));
		}
		/*List<Status> statuses = (List<Status>) manager.executeJDOSQL("SELECT FROM dataBase.Status");
		List<Status> newStatuses = new ArrayList<Status>();

		int numOfStatuses = keys.size();
		Status[] newStatusBuffer = new Status[numOfStatuses];

		int indexOfKey;

		for(Status currentStatus:statuses)
		{
			//currentStatus = statuses.get(i);
			indexOfKey = keys.indexOf(currentStatus.getKey());

			if (indexOfKey!=-1)
			{
				newStatusBuffer[indexOfKey] = currentStatus;			
			}
		}

		for (int i=0; i<numOfStatuses; i++)
		{
			if (newStatusBuffer[i]!=null)
				newStatuses.add(newStatusBuffer[i]);
		}*/

		return newStatuses;

	}

	public List<Level> keyToLevel(List<Key> keys)
	{
		List<Level> newLevels = new ArrayList<Level>();
		Level tmp=new Level();

		for(Key key: keys){
			newLevels.add((Level)manager.getObjectById(tmp, key));
		}
		/*List<Level> levels = (List<Level>) manager.executeJDOSQL("SELECT FROM dataBase.Level");
		List<Level> newLevels = new ArrayList<Level>();

		int numOfLevels = keys.size();
		Level[] newLevelsBuffer = new Level[numOfLevels];

		int indexOfKey;

		for(Level currentLevel:levels)
		{
			indexOfKey = keys.indexOf(currentLevel.getKey());

			if (indexOfKey!=-1)
			{
				newLevelsBuffer[indexOfKey] = currentLevel;			
			}
		}

		for (int i=0; i<numOfLevels; i++)
		{
			if (newLevelsBuffer[i]!=null)
				newLevels.add(newLevelsBuffer[i]);
		}*/

		return newLevels;

	}

	public List<Status> getStatusesOfBiz(String userName){
		Business b = getBiz(userName);

		if (b==null)
		{
			System.out.println("getStatusesOfBiz: "+userName);
			System.out.println("no such business");
			return null;
		}

		List<Key> keys = b.getStatusList();

		List<Status> newStatuses = keyToStatus(keys);

		return newStatuses;
	}

	private Status getStatusByKey(List<Status> statuses,Key key)
	{
		for (Status s:statuses)
		{
			if (key.equals(s.getKey()))
				return s;
		}

		return null;
	}

	public String getStatusName(Key levelKey){
		//System.out.println("key: "+levelKey);
		List<Key> l = new ArrayList<Key>();
		l.add(levelKey);
		return keyToLevel(l).get(0).getCurrentStatusString(); 
	}

	public List<Client> getClientsByPhone(PhoneNumber phone)
	{
		@SuppressWarnings("unchecked")
		List<Client> results = (List<Client>)manager.executeJDOSQL("SELECT FROM dataBase.Client " +
				"WHERE phoneNumber == \""+phone.getNumber() +"\"");
		System.out.println("$$$$$$$$$$$" + results.size());
		return results;
	}

	public List<Order> getClientOrders(String userName)
	{
		Client client = getClient(userName);
		System.out.println("username "+userName+ " client: "+client);
		return keyToOrders(client.getAllOrdersList());
	}

	public List<String> statusOfOrder(Order order)
	{
		List<Key> keysLevels = order.getLevelsList();
		List<String> statuses = new ArrayList<String>();

		for (Key k: keysLevels)
			statuses.add(getStatusName(k));


		return statuses;
	}


	/**
	 * 
	 * @param userNameClient
	 * @param bizName
	 * @param orderID
	 * @return 0 - success, 1- no such biz name, 2 - no such order id, 3 - no such client, 4 - phone not match
	 */
	public int addOrderToClient(String userNameClient, String bizUserName, int orderID, String phone, boolean statusNotification, boolean messageNotification)
	{

		Business b = getBiz(bizUserName);

		if (b==null)
		{
			System.out.println("no such biz name");
			return 1;
		}
		Order o = getOrder(orderID, b.getUserName());

		if (o==null)
		{
			System.out.println("no such order id");
			return 2;
		}

		Client client = getClient(userNameClient);

		if (client==null)
		{
			System.out.println("no such client");
			return 3;
		}

		if (!o.getPhoneNumber().getNumber().equals(phone))
		{
			System.out.println("phone not match");
			return 4;			
		}
		o.setStatusNotification(statusNotification);
		o.setMessageNotification(messageNotification);
		o.addClient(client.getKey());
		manager.Save(o);

		//		client.addToAllOrdersList(o);
		//		manager.Save(client);
		addOrderToClient(client,o,b.getBizType());
		return 0;

	}

	private int addOrderToClient(Client client, Order order, String bizType)
	{
		if (client==null || order==null)
			return 1;

		if (bizType==null)
		{
			Business biz = getBiz(order.getBusinessUserName());
			bizType = biz.getBizType();
		}

		client.addToAllOrdersList(order);
		client.addToOrdersType(bizType);
		manager.Save(client);
		order.addClient(client.getKey());
		manager.Save(order);
		return 0;		
	}

	public int sendMessage(String bizUserName, int orderID, Text message) {
		
		Business biz = getBiz(bizUserName);
		Order order = getOrder(orderID, biz);

		if (order==null)
			return 1;

		Date d = new Date();
		Text messageWithDate =  new Text(formatter.format(d) + " : " + message.getValue());
		order.addMessage(messageWithDate);
		order.setMessageToRead(true);
		manager.Save(order);

		List<Client> clients = getClientsByPhone(order.getPhoneNumber());

		addToHistoryLog(biz, "type : \""+HistoryLog.getInst().getSEND_MESSAGE_TYPE()+"\", \"orderNum\" : "+orderID + ", massage : \""+message + "\"", d);
		manager.Save(biz);
		
		String orderEmail = order.getEmail();
		boolean sendMail = order.getSendMail();
		boolean isEmailAlreadyExist = false;

		if ((clients!=null)&&(order.getMessageNotification().booleanValue()))
		{
			for (Client c: clients)
			{
				if (c.getEmail().getEmail().equals(orderEmail))
					isEmailAlreadyExist=true;

				MailingManager.getInst().newMessageMail(c.getUserName(), bizUserName, orderID,order.getPhoneNumber().getNumber(), message.getValue(), c.getEmail().getEmail(),false);
			}
		}

		//send also to the email that the biz added
		if (sendMail && (!orderEmail.equals("")) && (!isEmailAlreadyExist))
		{
			MailingManager.getInst().newMessageMail("", bizUserName, orderID,order.getPhoneNumber().getNumber(), message.getValue(), orderEmail,true);
		} 

		return 0;		
	}


	public byte[] retrieveImage(String bizName) 
	{
		//Business biz = getBizByName(bizName);
		Business biz = getBiz(bizName);
		if ((biz==null)||(biz.getLogoPic()==null))
			return null;

		return biz.getLogoPic().getBytes();    
	}


	public Colors getColors(String userNameBiz)
	{
		Business b = getBiz(userNameBiz);

		if (b==null)
		{
			System.out.println("no biz");
			return null;
		}

		return new Colors(b.getBackground(),b.getSecBackground(),b.getBorderColor());
	}

	public String getCurrentStatusOfOrder(Order order){
		System.out.println("order " +order);
		if (order==null)
		{
			System.out.println("no such order");
			return null;
		}

		Key key = order.getLevelsList().get(order.getLevelsList().size()-1);
		return getStatusName(key);
	}

	public LinkedHashMap<String,Integer> getStatusHash(String userNameBiz, Date from, Date to)
	{
		LinkedHashMap<String,Integer> statusHash = new LinkedHashMap<String, Integer>();
		List<Status> status = getStatusesOfBiz(userNameBiz);

		Key firstStatus = status.get(0).getKey();

		for (Status s: status)
			statusHash.put(s.getStatusName(),0);


		List<Order> orders = getAvailableOrdersOfBiz(userNameBiz);
		String currentStatus;
		int id = 1;
		for (Order o: orders)
		{
			System.out.println("$$$$$$$ compare id "+id);
			id++;
			//if (isDateAfter(o.getStartTime(),from, true) &&  !isDateAfter(o.getStartTime(), to, false)){
			if (checkDateBetweenDates(o.getStartTime(), from, to)){
				if (o.getStatusList().get(0).equals(firstStatus)){
					currentStatus = getCurrentStatusOfOrder(o);
					statusHash.put(currentStatus, statusHash.get(currentStatus)+1);
				}
			}
		}

		return statusHash;		
	}
	public ArrayList<HashMap<String, Integer>> getTotalOrders(String userNameBiz, Date from, Date to){
		List<Order> orders = getOrdersofBiz(userNameBiz);
		HashMap<String, Integer> openOrders = initMapByDay();
		HashMap<String, Integer> closeOrders = initMapByDay();

		SimpleDateFormat sdf = new SimpleDateFormat("EEE", Locale.US);
		Calendar calendar = Calendar.getInstance();

		
		for (Order order:orders){
			Date startDate = order.getStartTime();
			Date endDate = order.getEndTime();
			calendar.setTime(startDate);

			System.out.println("@@@@  "+calendar.get(Calendar.DAY_OF_WEEK));	
			if (checkDateBetweenDates(startDate, from, to)){
				String day = sdf.format(startDate);
				System.out.println("******* "+day);
				openOrders.put(day, openOrders.get(day)+1);
			}
			if (endDate!=null && checkDateBetweenDates(endDate, from, to)){
				String day = sdf.format(endDate);
				System.out.println("*******!!! "+day);
				closeOrders.put(day, closeOrders.get(day)+1);
			}			
		}
		ArrayList<HashMap<String, Integer>> totalOrders = new ArrayList<HashMap<String,Integer>>();
		totalOrders.add(openOrders);
		totalOrders.add(closeOrders);

		return totalOrders;
	}
	
	public HashMap<String, Integer> getTotalOrdersByPriority(Business biz, Date from, Date to){
		String userNameBiz = biz.getUserName();
		List<Order> orders = getOrdersofBiz(userNameBiz);
		HashMap<String, Integer> openOrders = new HashMap<String, Integer>();
		
		for (Integer i=0;i<3;i++){
			openOrders.put("delayed"+i.toString(), 0);
			openOrders.put(i.toString(), 0);
		}
		
		Calendar calendar = Calendar.getInstance();
		
		for (Order order:orders){
			Date startDate = order.getStartTime();
			calendar.setTime(startDate);

			System.out.println("@@@@  "+calendar.get(Calendar.DAY_OF_WEEK));
			
			if (checkDateBetweenDates(startDate, from, to)){
				if (order.isDelay(biz.getOrderUrgencyTime(), null))
					openOrders.put("delayed"+order.getUrgency(), openOrders.get("delayed"+order.getUrgency())+1);
				else
					openOrders.put(order.getUrgency().toString(), openOrders.get(order.getUrgency().toString())+1);
			}
		}		

		return openOrders;
	}
	private LinkedHashMap<String, Integer> initMapByDay() {
		LinkedHashMap<String, Integer> orders = new LinkedHashMap<String, Integer>();
		orders.put("Sun", 0);
		orders.put("Mon", 0);
		orders.put("Tue", 0);
		orders.put("Wed", 0);
		orders.put("Thu", 0);
		orders.put("Fri", 0);
		orders.put("Sat", 0);
		
		return orders;
		
	}
	private boolean checkDateBetweenDates(Date origin, Date from, Date to){
		if (isDateAfter(origin,from, true) &&  !isDateAfter(origin, to, false))
			return true;
		return false;
	}
	/*
	 * check if first date is after the second date
	 */
	private boolean isDateAfter(Date firstDate, Date secondDate, boolean defult){
		/*SimpleDateFormat dayFormat = new SimpleDateFormat("dd");
		SimpleDateFormat monthFormat = new SimpleDateFormat("MM");
		SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");*/
		ArrayList<SimpleDateFormat> formatList = new ArrayList<SimpleDateFormat>();


		formatList.add(new SimpleDateFormat("yyyy"));
		formatList.add(new SimpleDateFormat("MM"));
		formatList.add(new SimpleDateFormat("dd"));
		System.out.println("**********compare date "+firstDate +"to "+secondDate);
		for(SimpleDateFormat sdf : formatList){
			if (Integer.parseInt(sdf.format(firstDate)) < Integer.parseInt(sdf.format(secondDate)))
				return false;
			if (Integer.parseInt(sdf.format(firstDate)) > Integer.parseInt(sdf.format(secondDate)))
				return true;
		}
		return defult;
	}
	public String fetchPassword(String userNameBiz)
	{
		Business biz = getBiz(userNameBiz);
		return biz.getPassword();
	}



	//	public String getCurrentStatus(String userNameBiz,Order order)
	//	{
	//		
	//		getStatus(userNameBiz,order.getLevelsList().)
	//	}

	public List<Business> allBiz()
	{
		@SuppressWarnings("unchecked")
		List<Business> results = (List<Business>) manager.executeJDOSQL("SELECT FROM dataBase.Business ORDER BY bizName ASCENDING");

		return results;
	}
	//0-success 1- order not exist
	public int setMessageToRead(int orderID,String userNameBiz,boolean newMessageToRead) {

		Order order = getOrder(orderID, userNameBiz);
		if (order==null)
			return 1;

		System.out.println("newMessageToRead "+newMessageToRead);
		order.setMessageToRead(newMessageToRead);
		manager.Save(order);
		return 0;

	}

	/**
	 * 
	 * @param userNameBiz
	 * @param orderID
	 * @param statusNotification
	 * @param messageNotification
	 * @return 0 - succeed, 1 - order not exist
	 */
	public int orderSettingClient(String userNameBiz,int orderID, boolean statusNotification, boolean messageNotification) 
	{
		Order order = getOrder(orderID, userNameBiz);

		if (order==null)
			return 1;

		order.setStatusNotification(statusNotification);
		order.setMessageNotification(messageNotification);
		manager.Save(order);

		return 0;		
	}

	/**
	 * 
	 * @param orderID
	 * @param text
	 * @param destAddress
	 * @param userNameBiz
	 * @param newPhone
	 * @param email
	 * @param sendMailBoolean
	 * @param details
	 * @return 0 - succeed, 1 - order not exist
	 */
	public int orderSettingBusiness(int orderID, Text comments, String destAddress,
			String userNameBiz, PhoneNumber newPhone, Email email,
			boolean sendMailBoolean, String details, Integer orderUrgency) {

		Order order = getOrder(orderID, userNameBiz);

		if (order==null)
			return 1;		

		order.setComments(comments);
		order.setDestAdd(destAddress);
		order.setEmail(email.getEmail());
		order.setSendMail(sendMailBoolean);
		order.setDetails(details);
		order.setUrgency(orderUrgency);
		
		if (newPhone!=null)
			order.setPhoneNumber(newPhone);

		manager.Save(order);

		System.out.println("!!!!!!!update order setting");
		return 0;
	}

	/**
	 * 
	 * @param orderID
	 * @param userNameBiz
	 * @return 0 - succeed, 1 - order not exist, 2 - biz not exist
	 */
	public int cancelSendToOrderMail(int orderID,String userNameBiz) {

		String userName = AllBiz.getInst().bizNameToUserName(userNameBiz);
		System.out.println("##user name "+userName);

		if (userName==null)
			return 2;

		Order order = getOrder(orderID, userName);

		if (order==null)
			return 1;	

		order.setSendMail(false);		
		manager.Save(order);

		return 0;
	}

	/**
	 * 
	 * @param bizUserName
	 * @param imageBytes
	 * @return 0 - succeed. 1- no image. 2 - no biz.
	 */
	public int addBizAd(String bizUserName, byte[] imageBytes,String link) {

		Business biz = getBiz(bizUserName);

		if (imageBytes==null)
			return 1;

		if (biz==null)
			return 2;

		Blob blob = new Blob(imageBytes); 
		Ad ad = new Ad();
		ad.setAdPic(blob);
		ad.setAppeared(0);
		ad.setClicked(0);
		ad.setBusinessUserName(bizUserName);
		ad.setBizType(biz.getBizType());
		ad.setCreateDate(new Date());
		ad.setLink(link);
		manager.Save(ad);

		biz.addAd(ad);
		manager.Save(biz);
		return 0;
	}

	/**
	 * 
	 * @param bizUserName
	 * @param id
	 * @return 0 -succeed. 1- no such biz. 2 - no such ad.
	 */
	public int deleteAdBusiness(String bizUserName, int id) {
		Business biz = getBiz(bizUserName);
		if (biz==null)
			return 1;

		ArrayList<Key> adsKeys = biz.getAdsKeys();

		if (adsKeys==null || id<0 || id>=adsKeys.size())
			return 2;

		//remove from the blobArray and the keysArray business
		Key adToDelete = adsKeys.remove(id);
		biz.getAds().remove(id);
		manager.Save(biz);

		ArrayList<Key> adToDeleteArray = new ArrayList<Key>();
		adToDeleteArray.add(adToDelete);
		List<Ad> ads = keyToAds(adToDeleteArray);

		for (Ad ad:ads)
		{
			manager.Delete(ad);
		}

		return 0;
	}


	private Ad getRandomAd(Set<String> types)
	{
		if (types.isEmpty())
		{
			System.out.println("client ad types empty");
			return null;
		}
		String queryString = "SELECT FROM dataBase.Ad WHERE";

		for (String type:types)
		{
			queryString+= " bizType == \""+type+"\"";
		}

		List<Ad> results = (List<Ad>) manager.executeJDOSQL(queryString);
		int size = results.size();

		if (size==0)
			return null;

		int rnd = random.nextInt(size);

		return results.get(rnd);
	}

	public Ad getRandomAdByClient(String clientUserName)
	{
		Client client = getClient(clientUserName);
		Set<String> clientOrdersType = client.getOrdersTypes();
		Ad ad = getRandomAd(clientOrdersType);
		System.out.println("client ad "+clientUserName + " "+ad);
		return ad;

	}

	private HashMap<String, Long> createHash(int n)
	{
		HashMap<String, Long> hash = new HashMap<String, Long>();

		for (int i=1; i<=n; i++)
		{
			hash.put("sum"+i, new Long(0));
			hash.put("count"+i, new Long(0));			
		}

		return hash;
	}

	public void savebiz(Business b){
		manager.Save(b);
	}
	public int deleteOrder(String userName, int orderID) {
		Order order = getOrder(orderID, userName);

		if(order==null)
			return 1;
		Business business = getBiz(userName);
		Key key = order.getKey();
		List<Key> orderListKey =business.getAllOrdersList();
		orderListKey.remove(key);
		manager.Save(business);
		List<Client> clientList = keyToClient(order.getClientList());
		System.out.println("sizeeeeeeeeeeeeeeeee: "+clientList.size());
		for(Client client : clientList){
			client.removeOrder(order.getKey());
			manager.Save(client);
		}


		manager.Delete(order);
		return 0;
	}
	public List<Client> keyToClient(List<Key> keys) {
		List<Client> clientsList = new ArrayList<Client>();

		Client tmp=new Client();
		
		for(Key key: keys){
			clientsList.add((Client)manager.getObjectById(tmp, key));
		}
		return clientsList;
	}
	
	public ArrayList<LinkedHashMap<String, Double>> timesStatistic(String bizUserName, Date from, Date to)
	{
		System.out.println("-----------"+from+"  "+to);
		String queryString = "SELECT FROM dataBase.Level WHERE bizUserName  == \""+bizUserName+"\"";
		List<Level> results = (List<Level>) manager.executeJDOSQL(queryString);

		int numOfMounth =12;
		LinkedHashMap<String, Long> sum = new LinkedHashMap<String, Long>();
		HashMap<String, Integer> count = new HashMap<String, Integer>();
		HashMap<String,HashMap<String,Long>> statusSumCount = new HashMap<String, HashMap<String,Long>>();
		List<Status> statuses = getStatusesOfBiz(bizUserName);

		for (Status status: statuses)
		{
			sum.put(status.getStatusName(), new Long(0));
			count.put(status.getStatusName(), 0);
			statusSumCount.put(status.getStatusName(), createHash(numOfMounth));
		}
		for (Level level:results)
		{
			if (level.getBizUserName()==null)
				continue;

			if (level.getEndTime()==null)
				continue;

			String status = level.getCurrentStatusString();

			long diff = level.getEndTime().getTime() - level.getStartTime().getTime();
			Long statusSum = sum.get(status);
			Integer statusCount = count.get(status);
			System.out.println(status+"   diff = "+diff+"  status sum = "+statusSum);
			//check if the status is relevant
			if (statusSum==null)
				continue;
			if (checkDateBetweenDates(level.getEndTime(), from, to)){
				sum.put(status, statusSum+diff);
				count.put(status, statusCount+1);	

				System.out.println("yes!! "+level.getEndTime()+" between "+from+"  and "+to);
				HashMap<String, Long> statusHash = statusSumCount.get(status);
				int i = getCategory(level.getEndTime());
				statusHash.put("sum"+i, statusHash.get("sum"+i)+diff);
				statusHash.put("count"+i, statusHash.get("count"+i)+1);

			}

		}

		LinkedHashMap<String, Double> avg = new LinkedHashMap<String, Double>();
		ArrayList<LinkedHashMap<String, Double>> allData = new ArrayList<LinkedHashMap<String,Double>>();
		allData.add(avg);

		String statusName;
		for (Status status: statuses)
		{
			statusName = status.getStatusName();
			System.out.println(" status = "+statusName+"   "+sum.get(statusName)+"   "+(double)count.get(statusName));

			if (count.get(statusName)==0)
				avg.put(statusName, 0.0);
			else
				avg.put(statusName, sum.get(statusName)/((double)count.get(statusName)));

			HashMap<String, Long> statusHash = statusSumCount.get(statusName);
			LinkedHashMap<String, Double> statusAvg = new LinkedHashMap<String, Double>();

			for (Integer i=1; i<=numOfMounth; i++)
			{
				if (statusHash.get("count"+i)==0)
					statusAvg.put(i.toString(), 0.0);
				else
					statusAvg.put(i.toString(), statusHash.get("sum"+i)/((double)statusHash.get("count"+i)));
			}

			allData.add(statusAvg);
		}

		System.out.println("avg: "+avg);
		return allData;

	}



	private int getCategory(Date date)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("MM");
		return Integer.parseInt(sdf.format(date));

	}

	public Order keyToOrder(long id)
	{
		Order tmp=new Order(); 
		return (Order)manager.getObjectById(tmp, id);
	}
	public int register(String userName, String registrationId) {
		Client client = getClient(userName);
		if(client==null)
			return 1;
		client.setRegistId(registrationId);
		manager.Save(client);
		return 0;
		
		
	}
	
	public int unregister(String userName) {
		Client client = getClient(userName);
		if(client==null)
			return 1;
		client.setRegistId(null);
		manager.Save(client);
		return 0;
	}

	public String adClicked(long adKey) {

		Ad ad = keyToAd(adKey);
		ad.setClicked(ad.getClicked()+1);
		manager.Save(ad);
		String bizUserName = ad.getBusinessUserName();
		Business business = getBiz(bizUserName);
		if(ad.getLink()!=null)
			return ad.getLink();
		return "/businessPage.jsp?bizKey="+business.getKey().getId();
		
	}
	public void adAppeared(long id) {
		Ad ad = keyToAd(id);
		ad.setAppeared(ad.getAppeared()+1);
		manager.Save(ad);
		
	}
	public List<Ad> getAdsCount(String userNameBiz) {
		Business business = getBiz(userNameBiz);
		if(business!=null){
			List<Key> keys = business.getAdsKeys();
			return keyToAds(keys);
		}
		return new ArrayList<Ad>();
	}
	
	public void addVote(String userNameBiz, int orderID, int vote){
		Business biz = getBiz(userNameBiz);
		Order order = getOrder(orderID, biz);
		if(biz==null || order==null){
			System.out.println("addVote Error: no biz/order (null) ");
			return;
		}
		double voteAvg = biz.getVotesAvg();
		int numOfVotes = biz.getNumOfVotes();
		if(order.getVote()!=0)			
			biz.setVotesAvg((voteAvg*numOfVotes-order.getVote()+vote)/numOfVotes);
		else{
			biz.setVotesAvg((voteAvg*numOfVotes+vote)/(numOfVotes+1));
			biz.setNumOfVotes(numOfVotes+1);
		}
		manager.Save(biz);
		order.setVote(vote);	
		
		manager.Save(order);
		
	}
	
	/*function doesn't save in DB*/
	private void addToHistoryLog(Business biz,String record, Date d)
	{
		if (d==null)
			d = new Date();
		
		String dateString = formatter.format(d);
	
		String jsonString;
		
		if (!record.equals(""))
			jsonString = "{date : \""+dateString + "\" ,"+record + "}";
		else
			jsonString = "{date :"+dateString + "}";
		
		biz.addHistoryLog(jsonString);
		
		
	}
	
	
	/*didn't check*/
	private void addToHistoryLog(Business biz,HashMap<String, String> hash, Date d)
	{
		String record = ""; //"{";
		
		Set<String> keySet = hash.keySet();
		boolean first = true;
		
		for (String attribute: keySet)
		{	
			if (!first)
				record += " , ";
			else
				first = false;
			
			record += "\""+attribute + "\" : " + "\""+hash.get(attribute)+"\"";
		}
		
		//record += "}";
		addToHistoryLog(biz, record, d);
		
		
	}
	

	
}
